package org.wenshu.ai.modular.chat.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import dev.langchain4j.data.embedding.Embedding;
import dev.langchain4j.model.chat.response.ChatResponse;
import dev.langchain4j.model.chat.response.StreamingChatResponseHandler;
import dev.langchain4j.model.input.Prompt;
import dev.langchain4j.model.input.PromptTemplate;
import dev.langchain4j.store.embedding.EmbeddingMatch;
import jakarta.annotation.Resource;
import lombok.Data;



import dev.langchain4j.data.document.Document;
import dev.langchain4j.data.document.DocumentSplitter;
import dev.langchain4j.data.document.splitter.DocumentSplitters;
import dev.langchain4j.data.segment.TextSegment;
import dev.langchain4j.model.embedding.EmbeddingModel;
import dev.langchain4j.store.embedding.EmbeddingStoreIngestor;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.MediaType;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.mvc.method.annotation.SseEmitter;
import org.wenshu.ai.modular.chat.controller.param.*;
import org.wenshu.ai.modular.chat.controller.param.*;
import org.wenshu.ai.modular.chat.controller.param.*;
import org.wenshu.ai.modular.chat.provider.core.AiModelService;
import org.wenshu.ai.modular.datasource.entity.AiDatasourceConfig;
import org.wenshu.ai.modular.datasource.param.AiDatasourceConfigIdParam;
import org.wenshu.ai.modular.datasource.service.AiDatasourceConfigService;
import org.wenshu.ai.modular.traindata.entity.TrainData;
import org.wenshu.ai.modular.traindata.service.TrainDataService;
import org.wenshu.auth.core.pojo.SaBaseLoginUser;
import org.wenshu.auth.core.util.StpLoginUserUtil;
import org.wenshu.common.pojo.CommonResult;
import org.wenshu.ai.modular.chatrecord.entity.AiChatRecord;
import org.wenshu.ai.modular.chatrecord.service.AiChatRecordService;

import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

import static java.time.Duration.ofSeconds;

@Tag(name = "大模型对话控制器")
@RestController
@Validated
@Slf4j

public class AiChatController {

    @Resource
    private AiModelService aiModelService;

    @Resource
    private AiChatRecordService aiChatRecordService;

    @Resource
    private AiDatasourceConfigService aiDatasourceConfigService;

    @Resource
    private TrainDataService trainDataService;


    // 初始化向量存储

    @Operation(summary = "OpenAI兼容的流式聊天接口")
    @PostMapping(value = "/chat/completions", produces = MediaType.TEXT_EVENT_STREAM_VALUE)
    public SseEmitter chatCompletions(@RequestBody ChatCompletionRequest request) {
        SseEmitter emitter = new SseEmitter(0L);
        
        // 获取最后一条用户消息
        String lastMessage = request.getMessages().get(request.getMessages().size() - 1).get("content");

        aiModelService.getStreamingChatModel().chat(lastMessage, new StreamingChatResponseHandler() {
            @Override
            public void onPartialResponse(String partialResponse) {
                try {
                    ChatCompletionChunk chunk = new ChatCompletionChunk();
                    chunk.setId("chatcmpl-" + UUID.randomUUID().toString());
                    chunk.setModel(request.getModel());
                    
                    ChatCompletionChoice choice = new ChatCompletionChoice();
                    choice.setIndex(0);
                    Map<String, String> delta = new HashMap<>();
                    delta.put("role", "assistant");
                    delta.put("content", partialResponse);
                    choice.setDelta(delta);
                    
                    chunk.setChoices(Collections.singletonList(choice));
                    
                    emitter.send(chunk);
                } catch (IOException e) {
                    onError(e);
                }
            }
            
            @Override
            public void onCompleteResponse(ChatResponse completeResponse) {
                try {
                    ChatCompletionChunk chunk = new ChatCompletionChunk();
                    chunk.setId("chatcmpl-" + UUID.randomUUID().toString());
                    chunk.setModel(request.getModel());
                    
                    ChatCompletionChoice choice = new ChatCompletionChoice();
                    choice.setIndex(0);
                    choice.setFinishReason("stop");
                    Map<String, String> delta = new HashMap<>();
                    delta.put("role", "assistant");
                    delta.put("content", "");
                    choice.setDelta(delta);
                    
                    chunk.setChoices(Collections.singletonList(choice));
                    
                    emitter.send(chunk);
                    emitter.complete();
                } catch (Exception e) {
                    onError(e);
                }
            }
            
            @Override
            public void onError(Throwable error) {
                try {
                    emitter.send(SseEmitter.event()
                            .data(Map.of("error", error.getMessage()))
                            .build());
                    emitter.complete();
                } catch (IOException e) {
                    emitter.completeWithError(e);
                }
            }
        });
        
        return emitter;
    }

    @Operation(summary = "发送对话消息")
    @PostMapping(value = "/ai/chat/send", produces = MediaType.TEXT_EVENT_STREAM_VALUE)
    public SseEmitter sendMessage(@RequestBody AiChatRequest request) {
        SseEmitter emitter = new SseEmitter(0L);
        SaBaseLoginUser loginUser = StpLoginUserUtil.getLoginUser();

        // 保存用户消息记录
        AiChatRecord chatRecord = new AiChatRecord();
        chatRecord.setUserId(loginUser.getId());
        chatRecord.setSessionId(request.getSessionId());
        chatRecord.setConfigId("999");
        chatRecord.setRole("user");
        chatRecord.setContent(request.getMessage());
        aiChatRecordService.save(chatRecord);

        // 创建AI回复记录
        AiChatRecord aiRecord = new AiChatRecord();
        aiRecord.setUserId(loginUser.getId());
        aiRecord.setConfigId("999");
        aiRecord.setSessionId(request.getSessionId());
        aiRecord.setRole("assistant");
        aiRecord.setContent("");


        // 使用流式模型进行对话
        aiModelService.getStreamingChatModel().chat(request.getMessage(), new StreamingChatResponseHandler() {
            @Override
            public void onPartialResponse(String partialResponse) {
                try {
                    emitter.send(SseEmitter.event()
                            .data(partialResponse)
                            .build());
                } catch (IOException e) {
                    onError(e);
                }
            }

            @Override
            public void onCompleteResponse(ChatResponse completeResponse) {
                try {
                    // 保存完整的AI回复
                    aiRecord.setContent(completeResponse.aiMessage().text());
                    aiChatRecordService.save(aiRecord);
                    emitter.complete();
                } catch (Exception e) {
                    onError(e);
                }
            }

            @Override
            public void onError(Throwable error) {
                try {
                    emitter.send(SseEmitter.event()
                            .data("Error: " + error.getMessage())
                            .build());
                    emitter.complete();
                } catch (IOException e) {
                    emitter.completeWithError(e);
                }
            }
        });

        return emitter;
    }


    @Operation(summary = "获取会话列表")
    @GetMapping("/ai/chat/sessions")
    public CommonResult<List<ChatSessionVO>> getSessions() {
        SaBaseLoginUser loginUser = StpLoginUserUtil.getLoginUser();
        
        // 查询当前用户的所有会话记录
        LambdaQueryWrapper<AiChatRecord> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(AiChatRecord::getUserId, loginUser.getId())
               .select(AiChatRecord::getSessionId, AiChatRecord::getContent, AiChatRecord::getCreateTime)
               .orderByDesc(AiChatRecord::getCreateTime);
        
        List<AiChatRecord> records = aiChatRecordService.list(wrapper);
        
        // 按会话ID分组，获取每个会话的最新消息
        Map<String, List<AiChatRecord>> sessionGroups = records.stream()
                .collect(Collectors.groupingBy(AiChatRecord::getSessionId));
        
        List<ChatSessionVO> sessions = sessionGroups.entrySet().stream().map(entry -> {
            String sessionId = entry.getKey();
            List<AiChatRecord> sessionRecords = entry.getValue();
            AiChatRecord lastRecord = sessionRecords.get(0); // 最新的消息
            
            ChatSessionVO vo = new ChatSessionVO();
            vo.setSessionId(sessionId);
            vo.setLastMessage(lastRecord.getContent());
            vo.setTitle("会话 " + sessionId); // 可以根据需要设置会话标题
            vo.setCreateTime(lastRecord.getCreateTime());
            return vo;
        }).collect(Collectors.toList());
        
        return CommonResult.data(sessions);
    }

    @Operation(summary = "获取会话消息记录")
    @GetMapping("/ai/chat/messages/{sessionId}")
    public CommonResult<List<AiChatRecord>> getMessages(@PathVariable String sessionId) {
        SaBaseLoginUser loginUser = StpLoginUserUtil.getLoginUser();
        
        // 查询指定会话的所有消息
        LambdaQueryWrapper<AiChatRecord> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(AiChatRecord::getUserId, loginUser.getId())
               .eq(AiChatRecord::getSessionId, sessionId)
               .orderByAsc(AiChatRecord::getCreateTime);
        
        List<AiChatRecord> messages = aiChatRecordService.list(wrapper);
        return CommonResult.data(messages);
    }

    @Operation(summary = "删除会话")
    @PostMapping("/ai/chat/delete/{sessionId}")
    public CommonResult<String> deleteSession(@PathVariable String sessionId) {
        SaBaseLoginUser loginUser = StpLoginUserUtil.getLoginUser();
        
        // 删除指定会话的所有消息
        LambdaQueryWrapper<AiChatRecord> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(AiChatRecord::getUserId, loginUser.getId())
               .eq(AiChatRecord::getSessionId, sessionId);
        
        aiChatRecordService.remove(wrapper);
        return CommonResult.ok();
    }
    @Operation(summary = "向量检索接口")
    @PostMapping("/ai/chat/vector-search")
    public CommonResult<VectorSearchResponse> vectorSearch(@RequestBody VectorSearchRequest request) {
        try {
            log.info("开始向量检索，查询内容：{}", request.getQuery());

            // 将用户查询转换为向量嵌入
            Embedding questionEmbedding = aiModelService.getEmbeddingModel().embed(request.getQuery()).content();

            // 设置检索参数
            int maxResults = 10;
            double minScore = 0.48;

            // 在各个向量库中检索相关内容
            List<EmbeddingMatch<TextSegment>> relevantDdls = aiModelService.getDdlEmbeddingStore().findRelevant(questionEmbedding, maxResults, minScore);
            List<EmbeddingMatch<TextSegment>> relevantDocs = aiModelService.getDocEmbeddingStore().findRelevant(questionEmbedding, maxResults, minScore);
            List<EmbeddingMatch<TextSegment>> relevantSqls = aiModelService.getSqlEmbeddingStore().findRelevant(questionEmbedding, maxResults, minScore);

            // 转换检索结果
            VectorSearchResponse response = new VectorSearchResponse();
            response.setDdlSegments(relevantDdls.stream().map(match -> match.embedded().text()).collect(Collectors.toList()));
            response.setDocSegments(relevantDocs.stream().map(match -> match.embedded().text()).collect(Collectors.toList()));
            response.setSqlSegments(relevantSqls.stream().map(match ->{
                String questionText = match.embedded().text();
                String embeddingId = match.embeddingId();
                // 根据id查询train Data的sql内容
                TrainData trainData = trainDataService.getOne(new LambdaQueryWrapper<TrainData>().eq(TrainData::getVectorId, embeddingId));
                String sqlText = trainData.getSqlText();
                return questionText+"\n"+sqlText;
            }).collect(Collectors.toList()));

            return CommonResult.data(response);
        } catch (Exception e) {
            log.error("向量检索失败", e);
            return CommonResult.error("向量检索失败: " + e.getMessage());
        }
    }

    @Operation(summary = "SQL生成接口")
    @PostMapping("/ai/chat/sql-generate")
    public CommonResult<SqlGenerateResponse> sqlGenerate(@RequestBody SqlGenerateRequest request) {
        try {
            // 构建SQL生成提示模板
            String template = """
                你是一个 {{sql_engine}} 数据库专家，请根据用户的自然语言查询和以下相关信息生成SQL语句。
                
                ### 数据库表结构信息:
                {{ddl_segments}}

                ### 业务文档信息:
                {{doc_segments}}

                ### 相似SQL示例:
                {{sql_segments}}

                ### 用户查询:
                {{query}}
                
                ### Instructions:
                - 确保只输出问题中要求的信息。如果问题要求特定的列，请确保 SELECT 子句中只包含该列，不要添加其他内容。
                - 生成的查询应返回问题中要求的所有信息，既不能遗漏，也不能多余。
                - 在生成最终 SQL 查询之前，请仔细思考编写查询的步骤。
                - 如果提供的上下文足够，请生成一个有效的SQL查询，无需对问题进行任何解释。
                - 如果提供的上下文不足，请解释为什么无法生成查询。
                - 请使用最相关的表。
                - 如果问题之前已被提问并回答过，请准确重复之前的答案。
                - 请生成一个有效的SQL查询语句，并确保它能在 {{sql_engine}} 数据库中正确执行。只返回SQL语句，不要包含其他解释，sql中不要带换行符。
                
               
                Take a deep breath and think step by step to find the correct SQL query.
                
                """;
            // todo - 如果提供的上下文几乎足够，但需要了解特定列中的特定字符串，请生成一个中间SQL查询以查找该列中的不同字符串。在查询前加上注释，注明 intermediate_sql


            PromptTemplate promptTemplate = PromptTemplate.from(template);

            AiDatasourceConfig datasourceConfig = aiDatasourceConfigService.getById(request.getDatasourceId());

            // 准备模板变量
            Map<String, Object> variables = new HashMap<>();
            variables.put("ddl_segments", String.join("\n", request.getContext().getDdlSegments()));
            variables.put("doc_segments", String.join("\n", request.getContext().getDocSegments()));
            variables.put("sql_segments", String.join("\n\n", request.getContext().getSqlSegments()));
            variables.put("query", request.getQuery());
            variables.put("sql_engine", datasourceConfig.getProvider());

            // 应用模板生成提示
            Prompt prompt = promptTemplate.apply(variables);

            // 调用大模型生成SQL
            String generatedSql = aiModelService.getChatModel().chat(prompt.text());
            generatedSql = cleanSqlString(generatedSql);

            SqlGenerateResponse response = new SqlGenerateResponse();
            response.setSql(generatedSql);

            return CommonResult.data(response);
        } catch (Exception e) {
            log.error("SQL生成失败", e);
            return CommonResult.error("SQL生成失败: " + e.getMessage());
        }
    }

    @Operation(summary = "SQL执行接口")
    @PostMapping("/ai/chat/sql-execute")
    public CommonResult<SqlExecuteResponse> sqlExecute(@RequestBody SqlExecuteRequest request) {
        try {
            // 获取数据源配置
            AiDatasourceConfigIdParam idParam = new AiDatasourceConfigIdParam();
            idParam.setId(request.getDatasourceId());
            AiDatasourceConfig config = aiDatasourceConfigService.detail(idParam);

            // 获取数据库连接并执行SQL
            Connection conn = DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword());
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(request.getSql());

            // 处理结果集
            List<Map<String, Object>> records = new ArrayList<>();
            List<String> columns = new ArrayList<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 获取列名
            for (int i = 1; i <= columnCount; i++) {
                columns.add(metaData.getColumnName(i));
            }

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(metaData.getColumnName(i), rs.getObject(i));
                }
                records.add(row);
            }

            // 关闭资源
            rs.close();
            stmt.close();
            conn.close();

            SqlExecuteResponse response = new SqlExecuteResponse();
            response.setColumns(columns);
            response.setRecords(records);

            return CommonResult.data(response);
        } catch (Exception e) {
            log.error("SQL执行失败", e);
            return CommonResult.error("SQL执行失败: " + e.getMessage());
        }
    }

    @Operation(summary = "SQL结果解释接口")
    @PostMapping("/ai/chat/sql-explain")
    public CommonResult<String> sqlExplain(@RequestBody SqlExplainRequest request) {
        try {
            String explanation = generateExplanation(request.getQuery(), request.getSql(), request.getResults());
            return CommonResult.data(explanation);
        } catch (Exception e) {
            log.error("结果解释失败", e);
            return CommonResult.error("结果解释失败: " + e.getMessage());
        }
    }


    @Operation(summary = "SQL自然语言查询接口")
    @PostMapping("/ai/chat/sqlchat")
    public CommonResult<SqlChatResponse> sqlChat(@RequestBody SqlChatRequest request) {
        SaBaseLoginUser loginUser = StpLoginUserUtil.getLoginUser();
        log.info("开始处理SQL自然语言查询，用户ID：{}，查询内容：{}", loginUser.getId(), request.getQuery());
        
        try {
            // 1. 初始化向量存储（实际应用中应该在应用启动时或单独的初始化方法中完成）
//                initializeVectorStores();

            // 2. 使用用户查询在向量数据库中检索相关信息
            // 将用户查询转换为向量嵌入
            log.info("开始进行向量嵌入转换");
            Embedding questionEmbedding = aiModelService.getEmbeddingModel().embed(request.getQuery()).content();
            
            // 设置检索参数
            int maxResults = 3;
            double minScore = 0.7;
            
            // 在各个向量库中检索相关内容
            log.info("开始在向量库中检索相关内容");
            List<EmbeddingMatch<TextSegment>> relevantDdls = aiModelService.getDdlEmbeddingStore().findRelevant(questionEmbedding, maxResults, minScore);
            List<EmbeddingMatch<TextSegment>> relevantDocs = aiModelService.getDocEmbeddingStore().findRelevant(questionEmbedding, maxResults, minScore);
            List<EmbeddingMatch<TextSegment>> relevantSqls = aiModelService.getSqlEmbeddingStore().findRelevant(questionEmbedding, maxResults, minScore);
            
            log.info("检索到DDL相关内容：{}条，文档相关内容：{}条，SQL相关内容：{}条",
                relevantDdls.size(), relevantDocs.size(), relevantSqls.size());
            
            // 转换检索结果为TextSegment列表
            List<TextSegment> ddlSegments = relevantDdls.stream().map(EmbeddingMatch::embedded).collect(Collectors.toList());
            List<TextSegment> docSegments = relevantDocs.stream().map(EmbeddingMatch::embedded).collect(Collectors.toList());
            List<TextSegment> sqlSegments = relevantSqls.stream().map(EmbeddingMatch::embedded).collect(Collectors.toList());
            
            // 构建提示，包含检索到的相关信息
            StringBuilder prompt = new StringBuilder();
            prompt.append("你是一个SQL专家，请根据用户的自然语言查询和以下相关信息生成SQL语句。\n\n");
            
            prompt.append("### 数据库表结构信息:\n");
            for (TextSegment ddl : ddlSegments) {
                prompt.append(ddl.text()).append("\n");
            }
            
            prompt.append("\n### 业务文档信息:\n");
            for (TextSegment doc : docSegments) {
                prompt.append(doc.text()).append("\n");
            }
            
            prompt.append("\n### 相似SQL示例:\n");
            for (TextSegment sql : sqlSegments) {
                prompt.append(sql.text()).append("\n");
            }
            
            prompt.append("\n用户查询: ").append(request.getQuery());
            prompt.append("\n\n请生成一个有效的SQL查询语句，并确保它能在数据库中正确执行。只返回SQL语句，不要包含其他解释。");
            
            // 4. 调用大模型生成SQL
            log.info("开始调用大模型生成SQL");
            String generatedSql = aiModelService.getChatModel().chat(prompt.toString());
            log.info("大模型生成的SQL：{}", generatedSql);
            
            // 5. 清理生成的SQL（移除可能的markdown格式等）
            generatedSql = cleanSqlString(generatedSql);
            // 使用前端传入的数据源ID
            AiDatasourceConfigIdParam idParam = new AiDatasourceConfigIdParam();
            idParam.setId(request.getDatasourceId());
            AiDatasourceConfig config = aiDatasourceConfigService.detail(idParam);

            // 获取数据库连接
            Connection conn = DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword());
            
            // 执行SQL查询
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(generatedSql);
            
            // 处理结果集
            List<Map<String, Object>> results = new ArrayList<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(metaData.getColumnName(i), rs.getObject(i));
                }
                results.add(row);
            }
            
            // 关闭资源
            rs.close();
            stmt.close();
            conn.close();
            
            // 7. 生成结果解释
            log.info("开始生成结果解释");
            String explanation = generateExplanation(request.getQuery(), generatedSql, results);
            
            // 8. 构建响应
            SqlChatResponse response = new SqlChatResponse();
            response.setSql(generatedSql);
            response.setResults(results);
            response.setExplanation(explanation);
            
            log.info("SQL自然语言查询处理完成");
            return CommonResult.data(response);
            
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.error("SQL生成或执行失败: " + e.getMessage());
        }
    }
    
    /**
     * 初始化向量存储
     * 实际应用中，这应该从数据库或文件系统加载数据
     */
    private void initializeVectorStores() {
        // 示例DDL信息
        List<Document> ddls = List.of(
             Document.from("CREATE TABLE sys_user (\n" +
                    "  id VARCHAR(20) PRIMARY KEY,\n" +
                    "  username VARCHAR(100) NOT NULL,\n" +
                    "  password VARCHAR(100) NOT NULL,\n" +
                    "  nick_name VARCHAR(100),\n" +
                    "  email VARCHAR(100),\n" +
                    "  phone VARCHAR(20),\n" +
                    "  create_time DATETIME\n" +
                    ");"),
            Document.from("CREATE TABLE sys_role (\n" +
                    "  id VARCHAR(20) PRIMARY KEY,\n" +
                    "  role_name VARCHAR(100) NOT NULL,\n" +
                    "  role_code VARCHAR(100) NOT NULL,\n" +
                    "  create_time DATETIME\n" +
                    ");"),
            Document.from("CREATE TABLE sys_user_role (\n" +
                    "  id VARCHAR(20) PRIMARY KEY,\n" +
                    "  user_id VARCHAR(20) NOT NULL,\n" +
                    "  role_id VARCHAR(20) NOT NULL,\n" +
                    "  create_time DATETIME\n" +
                    ");")
        );

        // 示例文档信息
        List<Document> docs = List.of(
            Document.from("系统用户表(sys_user)存储了所有系统用户信息，包括用户名、密码等基本信息。"),
            Document.from("系统角色表(sys_role)定义了系统中的各种角色，每个角色有唯一的角色编码。"),
            Document.from("用户角色关联表(sys_user_role)建立了用户和角色之间的多对多关系。")
        );

        // 示例SQL信息
        List<Document> sqls = List.of(
            Document.from("-- 查询所有用户信息\nSELECT * FROM sys_user;"),
            Document.from("-- 查询用户及其角色信息\nSELECT u.username, r.role_name \nFROM sys_user u \nJOIN sys_user_role ur ON u.id = ur.user_id \nJOIN sys_role r ON ur.role_id = r.id;"),
            Document.from("-- 查询特定角色的所有用户\nSELECT u.* \nFROM sys_user u \nJOIN sys_user_role ur ON u.id = ur.user_id \nJOIN sys_role r ON ur.role_id = r.id \nWHERE r.role_code = 'admin';")
        );

        // 将文档加入向量存储
        DocumentSplitter splitter = DocumentSplitters.recursive(100, 0);
        EmbeddingModel embeddingModel = aiModelService.getEmbeddingModel();
        EmbeddingStoreIngestor.builder()
            .documentSplitter(splitter)
            .embeddingModel(embeddingModel)
            .embeddingStore(aiModelService.getDdlEmbeddingStore())
            .build()
            .ingest(ddls);

        EmbeddingStoreIngestor.builder()
            .documentSplitter(splitter)
            .embeddingModel(embeddingModel)
            .embeddingStore(aiModelService.getDocEmbeddingStore())
            .build()
            .ingest(docs);

        EmbeddingStoreIngestor.builder()
            .documentSplitter(splitter)
            .embeddingModel(embeddingModel)
            .embeddingStore(aiModelService.getSqlEmbeddingStore())
            .build()
            .ingest(sqls);
    }
    
    /**
     * 清理生成的SQL字符串
     * 移除可能的markdown格式和其他非SQL内容
     */
    private String cleanSqlString(String sql) {
        // 移除可能的markdown代码块标记
        sql = sql.replaceAll("```sql", "").replaceAll("```", "");
        
        // 移除前后空白
        sql = sql.trim();
        sql = sql.replaceAll(";", "");

        return sql;
    }
    
    /**
     * 生成结果解释
     */
    private String generateExplanation(String query, String sql, List<Map<String, Object>> results) {
        StringBuilder explanation = new StringBuilder();
        
        // 构建提示
        String prompt = String.format(
            "用户查询: %s\n\n" +
            "生成的SQL: %s\n\n" +
            "查询结果: %s\n\n" +
            "请用简洁明了的语言解释这个SQL查询的结果，使非技术用户也能理解。解释应该包括:\n" +
            "1. 查询返回了什么数据\n" +
            "2. 结果的含义是什么\n" +
            "3. 如果结果为空，可能的原因是什么",
            query, sql, results.size() > 10 ? results.subList(0, 10) + "...等共" + results.size() + "条记录" : results
        );
        
        // 调用大模型生成解释
        String generatedExplanation = aiModelService.getChatModel().chat(prompt);
        
        return generatedExplanation;
    }
}


